USE [Tst]
GO

/* Drops */

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CasoExecucao]') AND type in (N'U'))
	DROP TABLE [dbo].[CasoExecucao]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PlanoExecucao]') AND type in (N'U'))
	DROP TABLE [dbo].[PlanoExecucao]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PlanoExecucaoStatus]') AND type in (N'U'))
	DROP TABLE [dbo].[PlanoExecucaoStatus]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CasoTeste]') AND type in (N'U'))
	DROP TABLE [dbo].[CasoTeste]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PlanoTeste]') AND type in (N'U'))
	DROP TABLE [dbo].[PlanoTeste]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TipoErro]') AND type in (N'U'))
	DROP TABLE [dbo].[TipoErro]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmbienteTeste]') AND type in (N'U'))
	DROP TABLE [dbo].[AmbienteTeste]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Modulo]') AND type in (N'U'))
	DROP TABLE [dbo].[Modulo]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Projeto]') AND type in (N'U'))
	DROP TABLE [dbo].[Projeto]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UsuarioPermissao]') AND type in (N'U'))
	DROP TABLE [dbo].[UsuarioPermissao]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usuario]') AND type in (N'U'))
	DROP TABLE [dbo].[Usuario]
GO

/* Create Tables */

CREATE TABLE Usuario ( 
	 cd_usuario INT NOT NULL IDENTITY	 
	,bit_ativo BIT NOT NULL	
	,ds_nome VARCHAR(250) NOT NULL 
	,ds_login VARCHAR(40) NOT NULL	
	,ds_senha VARCHAR(30) NOT NULL
	,ds_tipo VARCHAR(30) NOT NULL
	,CONSTRAINT PK_Usuario PRIMARY KEY (cd_usuario)	
);

CREATE TABLE UsuarioPermissao ( 
	 cd_usuario INT NOT NULL		
	,bit_cad_ambiente bit
	,bit_cad_cas_teste bit
	,bit_cad_pl_execucao bit
	,bit_cad_pl_teste bit
	,bit_cad_projeto bit
	,bit_cad_tp_erro bit
	,bit_vis_dash bit
	,bit_vis_rel bit
	,CONSTRAINT PK_UsuarioPermissao PRIMARY KEY (cd_usuario)
	,CONSTRAINT FK_UsuarioPermissao_cd_usuario FOREIGN KEY (cd_usuario) REFERENCES Usuario(cd_usuario)	
);

CREATE TABLE Projeto(
	 cd_projeto INT IDENTITY NOT NULL
	,ds_descricao VARCHAR(200) NOT NULL
	,ds_cliente VARCHAR(400) NOT NULL
	,ds_versao  VARCHAR(50) NOT NULL
	,dt_inicio DATE NOT NULL
	,cd_responsavel INT NOT NULL
	,CONSTRAINT PK_Projeto PRIMARY KEY (cd_projeto)
	,CONSTRAINT FK_Projeto_cd_responsavel FOREIGN KEY (cd_responsavel) REFERENCES Usuario(cd_usuario)	
);

CREATE TABLE Modulo(
	 cd_modulo INT IDENTITY NOT NULL
	,cd_projeto INT NOT NULL		
	,ds_descricao VARCHAR(200) NOT NULL
	,CONSTRAINT PK_Modulo PRIMARY KEY (cd_modulo)
	,CONSTRAINT FK_Modulo_cd_projeto FOREIGN KEY (cd_projeto) REFERENCES Projeto(cd_projeto)	
);

CREATE TABLE PlanoTeste(
	 cd_plano_teste INT IDENTITY NOT NULL
	,cd_projeto INT NOT NULL
	,cd_modulo INT NOT NULL
	,ds_descricao VARCHAR(200) NOT NULL
	,ds_criterios_termino NTEXT NULL
	,ds_documentos NTEXT NULL
	,ds_func_afetadas NTEXT NULL
	,ds_riscos NTEXT NULL
	,CONSTRAINT PK_PlanoTeste PRIMARY KEY (cd_plano_teste)
	,CONSTRAINT FK_PlanoTeste_cd_projeto FOREIGN KEY (cd_projeto) REFERENCES Projeto(cd_projeto)	
	,CONSTRAINT FK_PlanoTeste_cd_modulo FOREIGN KEY (cd_modulo) REFERENCES Modulo(cd_modulo)	
);

CREATE TABLE AmbienteTeste(
	 cd_ambiente_teste INT IDENTITY NOT NULL
	,ds_descricao VARCHAR(200) NOT NULL
	,CONSTRAINT PK_AmbienteTeste PRIMARY KEY (cd_ambiente_teste)			
);

CREATE TABLE TipoErro(
	 cd_tipo_erro INT IDENTITY NOT NULL
	,ds_descricao VARCHAR(200) NOT NULL
	,CONSTRAINT PK_TipoErro PRIMARY KEY (cd_tipo_erro)			
)


CREATE TABLE CasoTeste(
	 cd_caso_teste INT NOT NULL
	,cd_plano_teste INT NOT NULL
	,ds_descricao VARCHAR(200) NOT NULL
	,ds_detalhamento NTEXT NOT NULL
	,ds_pre_condicoes NTEXT NULL
	,ds_pos_condicoes NTEXT NULL
	,ds_massa NTEXT NULL
	,ds_criterios NTEXT NULL
	,ds_implementacao NTEXT NULL	
	,CONSTRAINT PK_CasoTeste PRIMARY KEY (cd_caso_teste)	
	,CONSTRAINT FK_CasoTeste_cd_plano_teste FOREIGN KEY (cd_plano_teste) REFERENCES PlanoTeste(cd_plano_teste)	
);

CREATE TABLE PlanoExecucaoStatus(
	 cd_status INT NOT NULL
	,ds_descricao VARCHAR(80) NOT NULL
	,CONSTRAINT PK_PlanoExecucaoStatus PRIMARY KEY (cd_status)
);

CREATE TABLE PlanoExecucao(
	 cd_plano_execucao INT NOT NULL
	,cd_plano_teste INT NOT NULL
	,ds_descricao VARCHAR(200) NOT NULL
	,cd_status INT NOT NULL
	,dt_inicio DATETIME NOT NULL
	,dt_conclusao DATETIME NULL
	,nr_tempo_gasto INT NULL	
	,CONSTRAINT PK_PlanoExecucao PRIMARY KEY (cd_plano_execucao)
	,CONSTRAINT FK_PlanoExecucao_cd_plano_teste FOREIGN KEY (cd_plano_teste) REFERENCES PlanoTeste(cd_plano_teste)	
	,CONSTRAINT FK_PlanoExecucao_cd_status FOREIGN KEY (cd_status) REFERENCES PlanoExecucaoStatus(cd_status)	
);

CREATE TABLE CasoExecucao(
	 cd_plano_execucao INT NOT NULL
	,cd_caso_teste INT NOT NULL
	,cd_ambiente_teste INT NOT NULL
	,cd_tipo_erro INT NOT NULL
	,ds_detalhamento NTEXT NULL
	,CONSTRAINT PK_CasoExecucao PRIMARY KEY (cd_plano_execucao,cd_caso_teste)
	,CONSTRAINT FK_CasoExecucao_cd_plano_execucao FOREIGN KEY (cd_plano_execucao) REFERENCES PlanoExecucao(cd_plano_execucao)
	,CONSTRAINT FK_CasoExecucao_cd_caso_teste FOREIGN KEY (cd_caso_teste) REFERENCES CasoTeste(cd_caso_teste)
	,CONSTRAINT FK_CasoExecucao_cd_ambiente FOREIGN KEY (cd_ambiente_teste) REFERENCES AmbienteTeste(cd_ambiente_teste)
	,CONSTRAINT FK_CasoExecucao_cd_tipo_erro FOREIGN KEY (cd_tipo_erro) REFERENCES TipoErro(cd_tipo_erro)
)

/* Insere dados default */

--admin
INSERT INTO Usuario(bit_ativo,ds_nome,ds_login,ds_senha,ds_tipo)VALUES(1,'Administrador','admin','admin','Administrador');

--dados fake
INSERT INTO Projeto(cd_responsavel,ds_cliente,ds_descricao,ds_versao,dt_inicio)VALUES(1,'CSN','Gerenciamento de Mudancas','3.01.49','03-30-2010');
INSERT INTO Modulo(cd_projeto,ds_descricao)VALUES(1,'Impacto');
INSERT INTO Modulo(cd_projeto,ds_descricao)VALUES(1,'Aprovacao');
INSERT INTO PlanoTeste(cd_modulo,cd_projeto,ds_descricao)VALUES(1,1,'PL.001');